import pymysql
import requests
from lxml import etree




def get_html(url):
    head={"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"}
    try:
        r = requests.get(url=url,headers=head)
        r.encoding = r.apparent_encoding
        r.raise_for_status()
        return r.text
    except Exception as e:
        print(e)


def parser(html):
    doc = etree.HTML(html)
    out_list = []
    for i in doc.xpath('//*[@id="content"]//ul/li'):
        title = i.xpath('div/h2/a/text()')[0].replace(' ','').replace("\n",'')
        score = i.xpath('div/p[1]/span[2]/text()')[0].replace(' ','').replace("\n",'')
        info = i.xpath('div/p[2]/text()')[0].replace(' ','').replace("\n",'').split('/')
        descibe = i.xpath('div/p[3]/text()')[0].replace(' ','').replace("\n",'')
        i_data = [title,
                  score,
                  info[0],
                  info[-2],
                  info[-1],
                  descibe
                  ]
        out_list.append(i_data)
    print(out_list)
    return out_list

def save_mysql(sql,val,**dbinfo):
    try:
        connect = pymysql.connect(**dbinfo)
        cursor = connect.cursor()
        cursor.executemany(sql,val)
        connect.commit()
    except Exception as err:
        connect.rollback()
        print(err)
    finally:
        cursor.close()
        connect.close()

if __name__ == '__main__':
    url='http://www.bspider.top/doubanbook/'
    html = get_html(url)
    out_list = parser(html)
    data = {
        "host":"127.0.0.1",
        "user":"root",
        "password":"root",
        "db":"spider",
        "charset":"utf8",
        "cursorclass":pymysql.cursors.DictCursor
    }
    sql = 'insert into bookinfo(bookname,score,autor,press,pubdate,describ)'\
    "values(%s,%s,%s,%s,%s,%s)"
    save_mysql(sql,out_list,**data)